const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, Op, QueryTypes} = require("../../models/db");
// 库存
router.post('/app/getGoodListWithKc', async ctx => {
    let tenantId= ctx.user.tenantId
    let uid= ctx.user.id
    let body= ctx.request.body

    let keyWord= body.keyWord?body.keyWord:''
    let goodCatId= body.goodCatId?body.goodCatId:''
    let buId= body.buId?body.buId:''
    let currentPage= body.currentPage
    let pageSize= body.pageSize?body.pageSize:30
    let offset = (currentPage - 1) * pageSize;
    let storage = await db.models['storage'].findOne({
        where: {
            tenantId,
            name: '默认仓库'
        }
    })
    if(keyWord) {
        let sql = `select id,name,spec,unit1,unit2 from v_goods where tenantId=${tenantId} `
        if(keyWord) {
            sql += ` and(name like '%${keyWord}%' or pinYin like '%${keyWord}%' or barCode  like '%${keyWord}%' or barCode1  like '%${keyWord}%') `
        }
        sql = `select a.*,iif(lastCount<>0,dbo.setAssQty(b.lastCount,a.spec,a.unit1,a.unit2),'') lastCount from (${sql}) a left join inventory b on a.id=b.invId and b.locationId=${storage.id} `
        let total = await db.query(`select count(*) total from (${sql}) a `,{type: QueryTypes.SELECT, plain: true})
        let list = await db.query(`select * from (${sql}) a order by id desc offset ${offset} rows fetch  next ${pageSize} rows only ;`,{type: QueryTypes})
        return ctx.suc('', {
            total: total.total,
            list
        })
    }
    else {
        if(goodCatId) {
            let sql = `select id,name,spec,unit1,unit2 from v_goods where tenantId=${tenantId} and categoryId=${goodCatId} `
            if(keyWord) {
                sql += ` and(name like '%${keyWord}%' or pinYin like '%${keyWord}%' or barCode  like '%${keyWord}%' or barCode1  like '%${keyWord}%') `
            }
            sql = `select a.*,iif(lastCount<>0,dbo.setAssQty(b.lastCount,a.spec,a.unit1,a.unit2),'') lastCount from (${sql}) a left join inventory b on a.id=b.invId and b.locationId=${storage.id} `
            let total = await db.query(`select count(*) total from (${sql}) a `,{type: QueryTypes.SELECT, plain: true})
            let list = await db.query(`select * from (${sql}) a order by id desc offset ${offset} rows fetch  next ${pageSize} rows only ;`,{type: QueryTypes})
            return ctx.suc('', {
                total: total.total,
                list
            })
        }
        else {
            let sql = `select id,name,spec,unit1,unit2 from v_goods where tenantId=${tenantId} `
            sql = `select a.*,iif(lastCount<>0,dbo.setAssQty(b.lastCount,a.spec,a.unit1,a.unit2),'') lastCount from (${sql}) a left join inventory b on a.id=b.invId and b.locationId=${storage.id} `
            let total = await db.query(`select count(*) total from (${sql}) a `,{type: QueryTypes.SELECT, plain: true})
            let list = await db.query(`select * from (${sql}) a order by id desc offset ${offset} rows fetch  next ${pageSize} rows only ;`,{type: QueryTypes})
            return ctx.suc('', {
                total: total.total,
                list
            })
        }
    }
})


module.exports = router;